package com.example.repository;


import com.example.entity.User;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface UserRepository extends Repository<User, Integer> {
    //查询 id 值最大的用户
    //使用@Query 注解可以自定义 JPQL
    @Query("select u from User u where u.id = (select max(u1.id) from User u1 )")
    User queryAnnotationMaxId();

    //使用占位符参数为 JPQL 语句传递参数值
    @Query("select u from User u where u.username = ?1 and u.phone = ?2")
    List<User> queryAnnotationParam1(String username, String phone);


    //使用命名参数为 JPQL 语句传递参数值
    @Query("select u from User u where u.username = :username and u.phone =:phone")
    List<User> queryAnnotationParam2(@Param("username") String username, @Param("phone") String phone);

    //Spring Data 允许在占位符参数中使用%
    @Query("select u from User u where u.username like %?1% or u.phone like %?2%")
    List<User> queryAnnotationLikeParam1(String username, String phone);


    //Spring Data 允许在命名参数中使用%
    @Query("select u from User u where u.username like %:username% or u.phone like %:phone%")
    List<User> queryAnnotationLikeParam2(@Param("username") String username, @Param("phone") String phone);

    //设置 nativeQuery = true，可以定义原生 sql 语句
    @Query(value = "select count(id) from user",nativeQuery = true)
    Long getTotalCount();


    //更新记录
    @Modifying
    @Query("update User u set u.phone = :phone where u.id = :id")
    void updateUserPhone(@Param("id") Integer id,@Param("phone")String phone);

    //插入新记录
    @Modifying
    @Query(value = "insert into user (gender,phone,username) values (?1,?2,?3)",nativeQuery = true)
    void insertUser(String gender,String phone,String username);


}